package com.demo.Test;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

public class Test9 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        int id=0;
        String name=null;
        String gender=null;
        int age=0;
        try{
            File file = new File("test.xls");
            Workbook wb = Workbook.getWorkbook(file);
            Sheet sheet = wb.getSheet(0);
            ArrayList<ArrayList> aa = new ArrayList<>();
            for(int i=0; i<sheet.getRows(); i++){
                ArrayList al = new ArrayList();
                for(int j=0; j<sheet.getColumns(); j++){
                    Cell cell = sheet.getCell(j, i);
                    al.add(cell.getContents());
                }
                aa.add(al);
            }
            String url= "jdbc:mysql://106.13.7.203:3306/wg12?useCursorFetch=true&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8";
            String username = "root";
            String password = "Asd6250390@";
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功");
            for(int i=1; i<aa.size(); i++) {
                ArrayList a = aa.get(i);
                for(int j=0; j<a.size(); j++){
                    switch ((aa.get(0)).get(j).toString()){
                        case "id": id= Integer.parseInt(a.get(j).toString());
                        break;
                        case "name": name= a.get(j).toString();
                        break;
                        case "gender": gender= a.get(j).toString();
                        break;
                        case "age": age= Integer.parseInt(a.get(j).toString());
                        break;
                    }
                }
                System.out.println(id);
                String sql ="insert into users(id, name, gender, age) values( '"+id+"', '"+name+"', '"+gender+"', '"+age+"' )";
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.execute();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
